<?php
namespace app\servercertificate\logic;
use \app\manage\logic\BaseLogic;
use \think\Db;
/**
 * @author:      李颖
 * @DateTime:    2018-02-01 10:41:17
 * @Description: 第二版本证书的变更
 */
class CertificateV2 extends BaseLogic
{
	protected $name = 'certificate';
    protected $config = 'db_config_certificate';
    protected $certificateModel = 'app\servercertificate\model\Certificate';

    // 前台获取出售中的证书列表
    // 每个商品可形成多个证书 针对该商品只显示该商品最后一次支付形成的证书 且处于可销售状态等条件
    // 证书相同字段一起连接
    // 证书不同字段一起连接且取最新一条【以支付时间排序】
    // 此处使用不了分页函数因为使用不了field函数
    // 执行sql语句：
    /*$sql = "SELECT `goods_id`,
        SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`sell_price`,`earnings`,`product_code`,`pay_time`,JSON_EXTRACT(goods_info, '$.goods_attrs.pattern.value'),JSON_EXTRACT(goods_info, '$.goods_attrs.standard.value')) ORDER BY pay_time DESC ),',',1) AS no_same_fields,
        GROUP_CONCAT( DISTINCT CONCAT_WS('-',JSON_EXTRACT(goods_info, '$.profile_pic'),JSON_EXTRACT(goods_info, '$.authors'),`name`) ) AS same_fields  FROM `tp_certificate` 
        WHERE  `is_sell` = 1  AND `sell_price` > 0  AND `is_pick` = 0  AND `apply_invalid` = 0  AND `is_transfer` = 0  AND `status` = 1 GROUP BY goods_id LIMIT 0,15";*/
    // to_user_name 限制一下
    public function isSellCertificateList($data): array 
    {
        
        $selectSql = "SELECT `goods_id`,MAX(create_time) AS max_create_time,
        SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`sell_price`,`earnings`,`product_code`,`pay_time`,JSON_EXTRACT(goods_info, '$.goods_attrs.pattern.value'),JSON_EXTRACT(goods_info, '$.goods_attrs.standard.value'),`id`) ORDER BY pay_time DESC ),',',1) AS no_same_fields,
        GROUP_CONCAT( DISTINCT CONCAT_WS('-',JSON_EXTRACT(goods_info, '$.profile_pic'),JSON_EXTRACT(goods_info, '$.authors'),`name`) ) AS same_fields  FROM `tp_certificate`";

        // 拼接where条件
        $_where = ' WHERE  `is_sell` = 1  AND `sell_price` > 0  AND `is_pick` = 0  AND `apply_invalid` = 0  AND `is_transfer` = 0  AND `status` = 1';
        $data['keywords'] && $_where .= ' AND `name` like "%' . $data['keywords'] . '%"';
        $data['cate_id'] && $_where .= ' AND `goods_cate_ids` like "%,' . $data['cate_id'] . ',%"';
        if($data['user_name'] && $data['user_id']){  
            $userNameWhere = ' AND ((user_id = ' . $data['user_id'] .') OR (user_id != ' . $data['user_id'] . ' AND (to_user_names LIKE "%,' . $data['user_name'] . ',%" OR to_user_names = "")))';
        }else{
            $userNameWhere = ' AND to_user_names = ""';
        }
        $_where .= $userNameWhere;
        // 拼接limit
        $limit = ' GROUP BY goods_id ORDER BY max_create_time desc LIMIT ' . bcmul($data['page']-1, $data['listRows']) . ',' . $data['listRows'];
        // 拼接sql语句
        $sql = $selectSql . $_where . $limit;
        $countSql = "SELECT `goods_id` FROM `tp_certificate`" . $_where . ' GROUP BY goods_id';
        // 执行语句
        $countRes = $this->db()->query($countSql);
        if(count($countRes) == 0){
            return null_data(null);
        }
        $list = $this->db()->query($sql);
        // 组装返回结果
        $res['total'] = count($countRes);
        $res['per_page'] = $data['listRows'];
        $res['current_page'] = $data['page'];
        $res['last_page'] = ceil($res['total'] / $data['listRows']);
        $splitList = $this->_splitToArray($list);
        $res['data'] = $this->_addOtherField($splitList);
        return auto_return($res, '成功获取在售证书列表', '获取在售证书列表失败');
    }
    // 拆分重组
    protected function _splitToArray($list): array 
    {
        $newList = [];
        foreach ($list as $k => $v) {
            $noSameFields = explode("-", $v['no_same_fields']);
            $sameFields = explode("-", $v['same_fields']);
            $one['id'] = $noSameFields[6];
            $one['goods_id'] = $v['goods_id'];
            $one['sell_price'] = $noSameFields[0];
            $one['earnings'] = $noSameFields[1];
            $one['product_code'] = $noSameFields[2];
            $one['pay_time'] = $noSameFields[3];
            $one['pattern'] = trim($noSameFields[4], '/"');
            $one['standard'] = trim($noSameFields[5], '/"');
            $one['profile_pic'] = trim($sameFields[0], '/"');
            $one['authors'] = trim($sameFields[1], '/"');
            $one['name'] = $sameFields[2];
            $newList[] = $one;
        }
        return $newList;
    }
    // 给该证书添加其余字段
    protected function _addOtherField($list): array 
    {
        $goodsIds = implode(',',array_column($list, 'goods_id'));
        $zero = strtotime(date('Y-m-d', time()));
        $sql = "SELECT goods_id, COUNT(*) AS counts, 
            SUM(certificate_price) AS sum_price, 
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time ASC ),',',1) AS first_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time DESC ),',',1) AS last_price 
            FROM tp_transfer_log WHERE create_time > " . $zero . " GROUP BY goods_id HAVING goods_id IN (" . $goodsIds . ")";
        $otherList = $this->db('transfer_log')->query($sql);
        $otherList = reset_array_key($otherList, 'goods_id');
        foreach ($list as $k => &$v) {
            $v['counts'] = $otherList[$v['goods_id']]['counts'] ?? 0; 
            $v['sum_price'] = $otherList[$v['goods_id']]['sum_price'] ?? 0;
            if(isset($otherList[$v['goods_id']]['first_price']) && $otherList[$v['goods_id']]['first_price']){
                $v['trend'] = bcdiv(bcsub($otherList[$v['goods_id']]['last_price'], $otherList[$v['goods_id']]['first_price']), $otherList[$v['goods_id']]['first_price'], 4) * 100;
            }else{
                $v['trend'] = 0;
            }
        }
        // 给指定的证书添加价格趋势数据
        $list = $this->_addPriceTrend($list);
        // 单独获取最新成交价
        $sql = "SELECT goods_id, 
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time DESC ),',',1) AS last_price 
            FROM tp_transfer_log GROUP BY goods_id HAVING goods_id IN (" . $goodsIds . ")";
        $newPriceList = $this->db('transfer_log')->query($sql);
        $newPriceList = reset_array_key($newPriceList, 'goods_id');
        foreach ($list as $k => &$v) {
            $v['newBargainPrice'] = $newPriceList[$v['goods_id']]['last_price'] ?? 0;
        }
        return $list;
    }
    // 给指定的证书添加3天内的价格趋势数据
    /*SELECT certificate_id,
        SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`create_time`,`certificate_price`) ORDER BY create_time ASC ),',',15) AS no_same_fields 
        FROM (SELECT certificate_id,certificate_price,create_time FROM tp_transfer_log WHERE create_time > 1518060257) AS tmp GROUP BY certificate_id HAVING certificate_id IN (1238,1239)*/
    protected function _addPriceTrend($list): array 
    {
        $threeZeroTime = bcsub(strtotime(date('Y-m-d', time())), 259200);
        $certiIds = "(" . implode(array_column($list, "id"), ",") . ")";
        $sql = "SELECT certificate_id,
                SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`create_time`,`certificate_price`) ORDER BY create_time ASC ),',',15) AS no_same_fields 
                FROM (SELECT certificate_id,certificate_price,create_time FROM tp_transfer_log WHERE create_time > " . $threeZeroTime . ") AS tmp GROUP BY certificate_id HAVING certificate_id IN " . $certiIds;
        $trendList = $this->db()->query($sql);
        foreach ($trendList as $k => &$v) {
            $temp = explode(",", $v['no_same_fields']);
            $trendX = $trendY = [];
            foreach ($temp as $kk => $vv) {
                $temp1 = explode("-", $vv);
                $trendX[] = $temp1[0];
                $trendY[] = $temp1[1];
            }
            $v['trend_data_x'] = $trendX;
            $v['trend_data_y'] = $trendY;
        }
        $trendList = reset_array_key($trendList, "certificate_id");
        foreach ($list as $k => &$v) {
            $v['trend_data_x'] = isset($trendList[$v['id']]) ? $trendList[$v['id']]['trend_data_x'] : [];
            $v['trend_data_y'] = isset($trendList[$v['id']]) ? $trendList[$v['id']]['trend_data_y'] : [];
        }
        return $list;
    }
    // 设置我的证书  转让中的证书不能进行修改价格
    public function setMyCertificate($data): array 
    {
        $data['is_sell'] !== '' && $saveData['is_sell'] = $data['is_sell'];
        $data['sell_price'] !== '' && $saveData['sell_price'] = $data['sell_price'];
        $data['to_user_names'] !== '' && $saveData['to_user_names'] = ',' . $data['to_user_names'] . ',';
        $_where['id'] = $data['id'];
        $_where['user_id'] = $data['user_id'];
        $_where['is_pick'] = 0;
        $_where['apply_invalid'] = 0;
        $_where['is_transfer'] = 0;
        $_where['status'] = 1;
        $res = $this->model($this->certificateModel)->save($saveData, $_where);
        return auto_return($res, '修改成功', '修改失败，请检查证书状态');
    }
    // 获取某一商品下的所有在售证书
    public function getTypeCertificateList($data): array 
    {
        $_where['goods_id'] = $data['goods_id'];
        $_where['is_sell'] = 1;
        $_whereOr = $data['user_name'] == '' ? 'to_user_names = ""' : 'to_user_names like "%,' . $data['user_name'] . ',%" OR to_user_names = ""';
        $_where['is_pick'] = 0;
        $_where['apply_invalid'] = 0;
        $_where['is_transfer'] = 0;
        $_where['status'] = 1;
        $list = $this->db()
                    ->field(['id','name','goods_id','goods_info$.profile_pic'=>'goods_profile_pic','product_code','sell_price','finish_distribute_time','earnings'])
                    ->where($_where)
                    ->where($_whereOr)
                    ->order('sell_price ' . $data['price_order'])
                    ->paginate(null,false,['page'=>$data['page'],'list_rows'=>$data['listRows']])
                    ->toArray();
        $list['data'] = $this->_addField($list['data']);
        return auto_return_page($list, '成功获取证书列表');
    }
    // 给在售证书进行添加其他字段
    protected function _addField($list): array 
    {
        // 获取昨日收益与今日收益  今日：今天零点->现在  昨日：今天零点-86400->今天零点
        $todayZero = strtotime(date('Y-m-d', NOW));
        $certificateIds = array_column($list, 'id');
        $_where['certificate_id'] = ['in', $certificateIds];
        $_where['status'] = 1;
        $_where['distribute_date_time'] = ['between',[$todayZero,NOW]]; // 今日收益
        $Db = $this->db('certificate_integral_distribute');
        $todayList = $Db->field('certificate_id,sum(integral) as sum_integral')->where($_where)->group('certificate_id')->select();
        $_where['distribute_date_time'] = ['between',[bcsub($todayZero, 86400),$todayZero]]; // 昨日收益
        $yesterdayList = $Db->field('certificate_id,sum(integral) as sum_integral')->where($_where)->group('certificate_id')->select();
        $todayList = reset_array_key($todayList, 'certificate_id');
        $yesterdayList = reset_array_key($yesterdayList, 'certificate_id');
        // 组装数据
        foreach ($list as $k => &$v) {
            $v['residue_earning_day'] = $v['finish_distribute_time'] > NOW ? bcdiv(bcsub($v['finish_distribute_time'], NOW), 86400, 2) : 0;
            $v['today_earning'] = isset($todayList[$v['id']]) ? $todayList[$v['id']]['sum_integral'] : 0;
            $v['yesterday_earning'] = isset($yesterdayList[$v['id']]) ? $yesterdayList[$v['id']]['sum_integral'] : 0;
            $v['goods_profile_pic'] = trim($v['goods_profile_pic'], '\"');
        }
        return $list;
    }
    // 对某一用户的证书进行还原设置
    public function restoreSettings($data): array 
    {
        $_where['id'] = $data['id'];
        $_where['is_transfer'] = 0;
        $_where['status'] = 1;
        $saveData['is_sell'] = 0;
        $saveData['sell_price'] = 0;
        $saveData['to_user_names'] = '';
        $res = $this->model($this->certificateModel)->save($saveData, $_where);
        return auto_return($res, '成功还原证书设置', '还原证书设置失败');

    }
    // 获取某证书的走势图  读取表格 transfer_log
    // 执行语句：
    /*$sql = "SELECT CONCAT( FROM_UNIXTIME( create_time, '%Y-%m-%d %H:' ) , FLOOR( FROM_UNIXTIME( create_time, '%i' ) /30 ) ) AS group_time, 
    COUNT(*) AS counts,
    SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time ASC ),',',1) AS opening_price,
    SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time DESC ),',',1) AS closing_price,
    SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY certificate_price ASC ),',',1) AS bottom_price,
    SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY certificate_price DESC ),',',1) AS top_price 
    FROM (SELECT certificate_id,`type`,certificate_type,certificate_price,`status`,create_time FROM tp_transfer_log WHERE certificate_id = " . $data['certificate_id'] . " AND `type` = 'sell' AND certificate_type = 'advanced' AND `status` = 1 ORDER BY create_time ASC) AS tmp 
    GROUP BY group_time";*/
    public function priceChart($data): array 
    {
        if($data['unit'] == '5m'){
            $groupTimeSql = "CONCAT( FROM_UNIXTIME( create_time, '%Y-%m-%d %H:' ) , FLOOR( FROM_UNIXTIME( create_time, '%i' ) /5 ) )";
        }elseif($data['unit'] == '15m'){
            $groupTimeSql = "CONCAT( FROM_UNIXTIME( create_time, '%Y-%m-%d %H:' ) , FLOOR( FROM_UNIXTIME( create_time, '%i' ) /15 ) )";
        }elseif($data['unit'] == '30m'){
            $groupTimeSql = "CONCAT( FROM_UNIXTIME( create_time, '%Y-%m-%d %H:' ) , FLOOR( FROM_UNIXTIME( create_time, '%i' ) /30 ) )";
        }elseif($data['unit'] == '1h'){
            $groupTimeSql = "CONCAT( FROM_UNIXTIME( create_time, '%Y-%m-%d ' ) , FLOOR( FROM_UNIXTIME( create_time, '%H' ) /1 ) )";
        }elseif($data['unit'] == '6h'){
            $groupTimeSql = "CONCAT( FROM_UNIXTIME( create_time, '%Y-%m-%d ' ) , FLOOR( FROM_UNIXTIME( create_time, '%H' ) /6 ) )";
        }elseif($data['unit'] == '1d'){
            $groupTimeSql = "CONCAT( FROM_UNIXTIME( create_time, '%Y-%m ' ) , FLOOR( FROM_UNIXTIME( create_time, '%d' ) /1 ) )";
        }
        if($data['scope'] == '1h'){
            $createTimeWhere = 'create_time between ' . bcsub(NOW, 3600) . ' and ' . NOW;
        }elseif($data['scope'] == '2h'){
            $createTimeWhere = 'create_time between ' . bcsub(NOW, 7200) . ' and ' . NOW;
        }elseif($data['scope'] == '6h'){
            $createTimeWhere = 'create_time between ' . bcsub(NOW, 21600) . ' and ' . NOW;
        }elseif($data['scope'] == '12h'){
            $createTimeWhere = 'create_time between ' . bcsub(NOW, 43200) . ' and ' . NOW;
        }elseif($data['scope'] == '1d'){
            $createTimeWhere = 'create_time between ' . bcsub(NOW, 86400) . ' and ' . NOW;
        }elseif($data['scope'] == '1w'){
            $createTimeWhere = 'create_time between ' . bcsub(NOW, 604800) . ' and ' . NOW;
        }elseif($data['scope'] == 'all'){
            $createTimeWhere = 'create_time > 0';
        }
        
        $sql = "SELECT " . $groupTimeSql . " AS group_time, 
            COUNT(*) AS counts,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time ASC ),',',1) AS opening_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time DESC ),',',1) AS closing_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY certificate_price ASC ),',',1) AS bottom_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY certificate_price DESC ),',',1) AS top_price 
            FROM (SELECT certificate_id,`type`,certificate_type,certificate_price,`status`,create_time FROM tp_transfer_log WHERE certificate_id = " . $data['certificate_id'] . " AND `type` = 'sell' AND certificate_type = 'advanced' AND `status` = 1 AND " . $createTimeWhere . " ORDER BY create_time ASC) AS tmp 
            GROUP BY group_time";
        $list = $this->db('transfer_log')->query($sql);
        $list = $this->_dealData($list, $data);
        if($list){
            $todayCount = $this->_addTodayCount($data);
            $res['list'] = $list;
            $res['todayCount'] = $todayCount; 
        }else{
            $res['list'] = [];
            $res['todayCount'] = ['top_price'=>0,'bottom_price'=>0,'counts'=>0,'trend'=>0]; 
        }
        return auto_return($res);
    }
    // 获取该证书的今日最高价最低价等数据(这里的价格为成交价 日涨跌：今天一天的涨跌)
    protected function _addTodayCount($data): array 
    {
        $todayZero = strtotime(date('Y-m-d', NOW));
        $goodsId = $this->model($this->certificateModel)->where(['id'=>$data['certificate_id']])->value('goods_id');
        $sql = "SELECT goods_id, COUNT(*) AS counts, 
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY certificate_price DESC ),',',1) AS top_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY certificate_price ASC ),',',1) AS bottom_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time ASC ),',',1) AS first_price,
            SUBSTRING_INDEX(GROUP_CONCAT( DISTINCT CONCAT_WS('-',`certificate_price`) ORDER BY create_time DESC ),',',1) AS last_price
            FROM tp_transfer_log WHERE create_time > " . $todayZero . " GROUP BY goods_id HAVING goods_id = " . $goodsId;
        $sqlRes = $this->db('transfer_log')->query($sql);
        if(isset($sqlRes[0])){
            $res['top_price'] = $sqlRes[0]['top_price'];
            $res['bottom_price'] = $sqlRes[0]['bottom_price'];
            $res['counts'] = $sqlRes[0]['counts'];
            $res['trend'] = bcdiv(bcsub($sqlRes[0]['last_price'], $sqlRes[0]['first_price']), $sqlRes[0]['first_price'], 4) * 100;
        }else{
            $res['top_price'] = 0;
            $res['bottom_price'] = 0;
            $res['counts'] = 0;
            $res['trend'] = 0;
        }
        return $res;
    }
    protected function _dealData($list, $data): array 
    {
        $num = (int)substr($data['unit'], 0, 1);
        $unit = substr($data['unit'], 1, 1);
        $newList = [];
        foreach ($list as $k => &$v) {
            if($unit == 'm'){
                $tmp = (int)substr($v['group_time'], 14) * $num;
                $groupTime = substr($v['group_time'], 0, 14) . str_pad($tmp, 2, "0", STR_PAD_LEFT);
            }else{
                $tmp = (int)substr($v['group_time'], 11) * $num;
                $groupTime = substr($v['group_time'], 0, 11) . str_pad($tmp, 2, "0", STR_PAD_LEFT) . ':00';
            }
            $v['group_time'] = $groupTime;
            $oneNew[0] = $v['group_time'];
            $oneNew[1] = (int)($v['opening_price']/100);
            $oneNew[2] = (int)($v['closing_price']/100);
            $oneNew[3] = (int)($v['bottom_price']/100);
            $oneNew[4] = (int)($v['top_price']/100);
            $oneNew[5] = (int)$v['counts'];
            $newList[] = $oneNew;
        }
        return $newList;
    }
    // 获取指向给我的定向单
    public function myDirectionalCerti($data): array 
    {
        $_where['is_sell'] = 1;
        $_where['sell_price'] = ['gt', 0];
        $_where['to_user_names'] = ['like', '%,' . $data['to_user_names'] . ',%'];
        $_where['type'] = 'advanced';
        $_where['is_pick'] = 0;
        $_where['apply_invalid'] = 0;
        $_where['is_transfer'] = 0;
        $_where['status'] = 1;
        $list = $this->model($this->certificateModel)
                    ->field('goods_info,order_info,transfer_log', true)
                    ->where($_where)
                    ->order('create_time desc')
                    ->paginate(null, false, ['page'=>$data['page'],'listRows'=>$data['listRows']])
                    ->toArray();
        return auto_return_page($list, '成功获取指向我的证书');
    }
    // 获取我的各种状态的高级证书  收益中、提货中、已提货、已出售、待接收
    // is_earning is_picking  is_picked  is_selled  is_receiving
    public function myAdvanceCertiByAction($data): array 
    {
        
        if($data['action'] == 'is_picking'){      // 获取提货中的证书
            return $this->_isPickingList($data);
        }elseif($data['action'] == 'is_selled'){    // 获取已出售的证书
            return $this->_isSelledList($data);
        }elseif($data['action'] == 'is_earning'){    // 获取收益中的证书
            $_where['c.user_id'] = $data['user_id'];
            $_where['c.is_pick'] = 0;
            $_where['pick_up_goods.pick_up_code'] = ['exp','is null'];
            // $_where['finish_distribute_time'] = ['egt', NOW];
        }elseif($data['action'] == 'is_picked'){     // 获取已提货的证书
            $_where['c.user_id'] = $data['user_id'];
            $_where['c.is_pick'] = 1;
        }elseif($data['action'] == 'is_receiving'){   // 获取待接收的证书
            $_where['c.is_pick'] = 0;
            $_where['c.to_user_names'] = ['like', '%,' . $data['user_name'] . ',%'];
            $_where['c.user_id'] = ['neq', $data['user_id']];
        }
        $_where['c.type'] = 'advanced';
        $_where['c.status'] = 1;
        $field = 'c.id,c.name,c.certificate_price,c.sell_price,c.earnings,c.product_code,c.goods_info,c.order_user_id,c.pay_time,c.finish_distribute_time';
        $list = $this->db()
                    ->alias('c')
                    ->field($field)
                    ->join('pick_up_goods', 'pick_up_goods.certificate_id = c.id', 'LEFT')
                    ->where($_where)
                    ->order('c.update_time desc')
                    ->paginate(null, false, ['page'=>$data['page'],'listRows'=>$data['listRows']])
                    ->toArray();
        if(!$list['total']){
            return verify_err(null, '未找到相应的记录');
        }
        $list['data'] = $this->_getPartGoodsInfoField($list['data']);
        $list['data'] = $this->_addButtonAndStateForList($list['data'], $data);
        return auto_return_page($list);
    }
    // 针对于一个证书添加按钮和状态
    protected function _addButtonAndState($oneCerti, $data): array 
    {
        $operateButton = [];
        if($data['action'] == 'is_picking'){   // 给提货中的证书添加按钮
            $operateButton[] = ['name'=>'提货详情','action'=>'onePickUpInfo'];
            if($oneCerti['transfer_status'] == 1){ //后台已发货
                $operateButton[] = ['name'=>'确认收货','action'=>'affirmPickUpGoods'];
                $oneCerti['general_state'] = '已发货/待提货';
            }else{
                $oneCerti['general_state'] = '待发货';
            }
        }elseif($data['action'] == 'is_selled'){
            $oneCerti['general_state'] = '已出售';
            $operateButton = [];
        }elseif($data['action'] == 'is_earning'){
            $oneCerti['general_state'] = $oneCerti['finish_distribute_time'] < NOW ? '收益结束' : '收益中';
            $operateButton[] = ['name'=>'出售','action'=>'setMyCertificate'];
            $data['user_id'] == $oneCerti['order_user_id'] && $operateButton[] = ['name'=>'退款/退证书','action'=>'sendBack']; 
            //判断是否达到提货时间
            if($oneCerti['start_delivery_time'] >= 0){
                $startDeliveryTime = bcmul($oneCerti['start_delivery_time'], 86400);
                $earliestPickTime = bcadd($oneCerti['pay_time'], $startDeliveryTime);
                $earliestPickDate = date('Y-m-d H:i', $earliestPickTime);
                if(NOW > $earliestPickTime){
                    $operateButton[] = ['name'=>'提货','action'=>'pickUpGoods'];
                }
            }
        }elseif($data['action'] == 'is_picked'){
            $oneCerti['general_state'] = '已提货';
            $operateButton[] = ['name'=>'提货详情','action'=>'onePickUpInfo'];
        }elseif($data['action'] == 'is_receiving'){
            $oneCerti['general_state'] = '待接收';
            $operateButton[] = ['name'=>'购买','action'=>'buy'];
            $operateButton[] = ['name'=>'拒绝','action'=>'refuseDirectional'];
        }
        $oneCerti['operating_button'] = $operateButton;
        return $oneCerti;
    }
    // 给列表数据添加按钮
    protected function _addButtonAndStateForList($dataList, $data): array
    {
        foreach ($dataList as $k => &$v) {
            $v = $this->_addButtonAndState($v, $data);
        }
        return $dataList;
    }
    // 获取提货中的证书
    protected function _isPickingList($data): array 
    {
        $_where['c.user_id'] = $data['user_id'];
        $_where['c.type'] = 'advanced';
        $_where['c.status'] = 1;
        $_where['c.finish_distribute_time'] = ['egt', NOW];
        $_where['pick_up_goods.pick_up_code'] = ['exp','is not null'];
        $_where['c.is_pick'] = 0;
        $field = 'c.id,c.name,c.certificate_price,c.sell_price,c.earnings,c.product_code,c.goods_info,pick_up_goods.pick_up_code,pick_up_goods.transfer_status';
        $list = $this->db()
                ->alias('c')
                ->field($field)
                ->join('pick_up_goods', 'pick_up_goods.certificate_id = c.id', 'LEFT')
                ->where($_where)
                ->order('c.update_time', 'desc')
                ->paginate(null,false,['page'=>$data['page'],'list_rows'=>$data['listRows']])
                ->toArray();
        if(!$list['total']){
            return verify_err(null, '未找到相应的记录');
        }
        $list['data'] = $this->_getPartGoodsInfoField($list['data']);
        $list['data'] = $this->_addButtonAndStateForList($list['data'], $data);
        return auto_return_page($list);
    }
    protected function _getPartGoodsInfoField($dataList): array 
    {
        foreach ($dataList as $k => &$v) {
            $goodsInfo = json_decode($v['goods_info'], true);
            $v['profile_pic'] = $goodsInfo['profile_pic'];
            $v['authors'] = $goodsInfo['authors'];
            $v['pattern'] = $goodsInfo['goods_attrs']['pattern']['value'];
            $v['standard'] = $goodsInfo['goods_attrs']['standard']['value'];
            $v['start_delivery_time'] = $goodsInfo['start_delivery_time'];
            unset($v['goods_info']);
        }
        return $dataList;
    }
    // 获取已出售的证书
    /*SELECT tmp.certificate_id,
               GROUP_CONCAT( DISTINCT CONCAT_WS('-',JSON_EXTRACT(goods_info, '$.profile_pic'),JSON_EXTRACT(goods_info, '$.authors'),`name`,`certificate_price`,`earnings`,`product_code`,JSON_EXTRACT(goods_info, '$.goods_attrs.pattern.value'),JSON_EXTRACT(goods_info, '$.goods_attrs.standard.value')) ) AS same_fields
        FROM 
               (SELECT c.id,c.name,c.certificate_price,c.earnings,c.product_code,c.goods_info,c.goods_id,c.status,t.certificate_id,t.`user_id`,t.`type` FROM tp_transfer_log AS t LEFT JOIN tp_certificate AS c ON c.id = t.`certificate_id`) AS tmp 
        WHERE tmp.user_id = 1067 AND tmp.`type` = 'sell' GROUP BY tmp.certificate_id*/
    protected function _isSelledList($data): array 
    {
        $sql = "SELECT tmp.certificate_id,
                   GROUP_CONCAT( DISTINCT CONCAT_WS('-',JSON_EXTRACT(goods_info, '$.profile_pic'),JSON_EXTRACT(goods_info, '$.authors'),`name`,`certificate_price`,`earnings`,`product_code`,JSON_EXTRACT(goods_info, '$.goods_attrs.pattern.value'),JSON_EXTRACT(goods_info, '$.goods_attrs.standard.value'),`sell_price`) ) AS same_fields
                FROM 
                       (SELECT c.id,c.name,c.certificate_price,c.sell_price,c.earnings,c.product_code,c.goods_info,c.goods_id,c.status,t.certificate_id,t.`user_id`,t.`type` FROM tp_transfer_log AS t LEFT JOIN tp_certificate AS c ON c.id = t.`certificate_id`) AS tmp 
                WHERE tmp.user_id = " . $data['user_id'] . " AND tmp.`type` = 'sell' GROUP BY tmp.certificate_id LIMIT " . bcmul($data['page']-1, $data['listRows']) . "," . $data['listRows'];
        $countSql = "SELECT certificate_id FROM tp_transfer_log WHERE user_id = " . $data['user_id'] . " AND `type` = 'sell' GROUP BY certificate_id";
        // 执行语句
        $countRes = $this->db()->query($countSql);
        if(count($countRes) == 0){
            return null_data(null);
        }
        $list = $this->db()->query($sql);
        // 组装返回结果
        $res['total'] = count($countRes);
        $res['per_page'] = $data['listRows'];
        $res['current_page'] = $data['page'];
        $res['last_page'] = ceil($res['total'] / $data['listRows']);
        $res['data'] = $this->_splitMyCertiToArray($list);
        $res['data'] = $this->_addButtonAndStateForList($res['data'], $data);
        return auto_return($res, '成功获取证书列表', '获取证书列表失败');
    }
    // 拆分字符为数组形式 我的证书列表
    protected function _splitMyCertiToArray($list): array 
    {
        $res = [];
        foreach ($list as $k => $v) {
            $sameFields = explode("-", $v['same_fields']);
            $one['id'] = $v['certificate_id'];
            $one['profile_pic'] = trim($sameFields[0], '/"');
            $one['authors'] = trim($sameFields[1], '/"');
            $one['name'] = trim($sameFields[2], '/"');
            $one['certificate_price'] = trim($sameFields[3], '/"');
            $one['earnings'] = trim($sameFields[4], '/"');
            $one['product_code'] = trim($sameFields[5], '/"');
            $one['pattern'] = trim($sameFields[6], '/"');
            $one['standard'] = trim($sameFields[7], '/"');
            $one['sell_price'] = trim($sameFields[8], '/"');
            $res[] = $one;
        }
        return $res;
    }
    // 获取不同状态证书列表的价格筛选项
    public function getCertificateFilter($data): array 
    {
        $data['type'] != 'all' && $_where['type'] = $data['type'];
        $_where['status'] = 1;

        $price = $this->db()
                    ->field(['max(certificate_price)'=>'max_certificate_price','min(certificate_price)'=>'min_certificate_price'])
                    ->where($_where)
                    ->find();
        $minPrice = bcsub($price['min_certificate_price'], $price['min_certificate_price']%100);
        if($price['max_certificate_price']%100){ 
            $maxPrice = bcadd($price['max_certificate_price'], bcsub(100, $price['max_certificate_price']%100));
        }else{
            $maxPrice = $price['max_certificate_price'];
        }
        $res = [];
        if(bcsub($maxPrice, $minPrice) < 100){
            $res[] = ['price_start'=>$minPrice,'price_end'=>$maxPrice,'symbol'=>'between'];
            return auto_success($res, '成功获取价格筛选项', '获取价格筛选项失败');
        }
        //间隔向上取整 保证结果后两位为0
        $d = ceil( bcdiv(bcsub($maxPrice, $minPrice), 5) );  
        if($d%100){
            $d = bcadd($d, bcsub(100, $d%100));
        }
        $topMod = $maxPrice%$d ? 1 : 0;
        $top =  bcmul($d, bcadd(floor( bcdiv($maxPrice, $d) ),$topMod));
        $bottom =  bcmul($d, floor( bcdiv($minPrice, $d) ));
        //组装结果
        $res[] = ['price_start'=>$bottom,'price_end'=>bcadd($bottom, $d),'symbol'=>'between'];
        $res[] = ['price_start'=>bcadd($bottom, $d),'price_end'=>bcadd($bottom, bcmul($d, 2)),'symbol'=>'between'];
        $res[] = ['price_start'=>bcadd($bottom, bcmul($d, 2)),'price_end'=>bcadd($bottom, bcmul($d, 3)),'symbol'=>'between'];
        $res[] = ['price_start'=>bcadd($bottom, bcmul($d, 3)),'price_end'=>bcadd($bottom, bcmul($d, 4)),'symbol'=>'between'];
        $res[] = ['price_start'=>bcadd($bottom, bcmul($d, 4)),'price_end'=>bcadd($bottom, bcmul($d, 5)),'symbol'=>'between'];
        /*if($topMod){ $res[] = ['price_start'=>bcadd($bottom, bcmul($d, 5)),'price_end'=>bcadd($bottom, bcmul($d, 6)),'symbol'=>'between']; }*/
        if($maxPrice > bcadd($bottom, bcmul($d, 5))){
            $res[] = ['price_start'=>bcadd($bottom, bcmul($d, 5)),'price_end'=>bcadd($bottom, bcmul($d, 6)),'symbol'=>'between'];
        }
        return auto_success($res, '成功获取价格筛选项', '获取价格筛选项失败');
    }


}